package com.qianfeng.shopping.dao;

import com.qianfeng.shopping.entity.*;
import com.qianfeng.shopping.util.ConnDb;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class LocationDao {
    // 查询所有省份
    public List<Province> selectAllPro(){
        Connection conn = ConnDb.getConnection();
        PreparedStatement pst = null;
        ResultSet rs = null;
        String sql="select * from province";
        List<Province> list = new ArrayList<>();
        try {
            pst = conn.prepareStatement(sql);
            rs = pst.executeQuery();
            while(rs.next()){
                Province province = new Province();
                province.setId(rs.getInt("id"));
                province.setCode(rs.getInt("code"));
                province.setName(rs.getString("name"));
                list.add(province);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            ConnDb.closeDb(rs,pst,conn);
        }
        return list;
    }
    // 通过省份查询城市
    public List<City> selectCity(int provincecode){
        Connection conn = ConnDb.getConnection();
        PreparedStatement pst = null;
        ResultSet rs = null;
        String sql="select * from city where provincecode=? ";
        List<City> list = new ArrayList<>();
        try {
            pst = conn.prepareStatement(sql);
            pst.setInt(1,provincecode);
            rs = pst.executeQuery();
            while(rs.next()){
                City city = new City();
                city.setId(rs.getInt("id"));
                city.setCode(rs.getInt("code"));
                city.setName(rs.getString("name"));
                city.setProviencecode(rs.getInt("provincecode"));
                list.add(city);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            ConnDb.closeDb(rs,pst,conn);
        }
        return list;
    }
    // 通过城市查询区县
    public List<Area> selectArea(int citycode){
        Connection conn = ConnDb.getConnection();
        PreparedStatement pst = null;
        ResultSet rs = null;
        String sql="select * from area where citycode=? ";
        List<Area> list = new ArrayList<>();
        try {
            pst = conn.prepareStatement(sql);
            pst.setInt(1,citycode);
            rs = pst.executeQuery();
            while(rs.next()){
                Area area = new Area();
                area.setId(rs.getInt("id"));
                area.setCode(rs.getInt("code"));
                area.setName(rs.getString("name"));
                area.setCitycode(rs.getInt("citycode"));
                list.add(area);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            ConnDb.closeDb(rs,pst,conn);
        }
        return list;
    }
    public static void main(String[] args) {
        LocationDao locationDao = new LocationDao();
//        System.out.println(locationDao.selectAllPro());
//        System.out.println(locationDao.selectCity(130000));
        System.out.println(locationDao.selectArea(210200));
    }
}
